﻿<?xml version="1.0" encoding="utf-8" ?>
<AdoNetDataAccess connection="$UserLoginDBConnection$" formatter="bizobjlist of User(groups)">
	<Sql>
		SELECT
			u.id, userName as useruserName, password, password as password2, companyId,
			membershipExpires, enable, l.groupId as groupsid, dateCreated, dateUpdated
		FROM
			Users u
		LEFT JOIN
			UserGroupLink l ON u.id = l.userId
		WHERE
			u.companyId=@companyId
    <code>
      <![CDATA[
		    if(criteria != null && ((IBizObjList)criteria.get("filterOptions")).numElements != 0)
		    {
          sql.Append(" AND ");
			    foreach(IBizObj filOp in criteria.get("filterOptions") as IBizObjList)
			    {
    		    string firstPerc = "", secondPerc = "", cx="x";
				    cx = filOp.get("criteria") as string;
				    if (string.Compare(cx, "s", true) == 0)
					    firstPerc = "%";
				    else if (string.Compare(cx, "e", true) == 0)
					    secondPerc = "%";
				    else if (string.Compare(cx, "c", true) == 0)
					    firstPerc = secondPerc = "%";
				    if (firstPerc.Length > 0 || secondPerc.Length > 0)
					    cx = "like";
				    if(filOp.get("field") != null && (string)filOp.get("field") != "")
				    {
					    sql.AppendFormat(" {0} {1} '{3}{2}{4}'", filOp.get("field"), cx, filOp.get("value"), firstPerc, secondPerc);
					    sql.Append(criteria.get("filterIsAnd").Equals(true)?" AND":"  OR");
				    }
			    }
			    sql.Length -= 4;
        }
    ]]></code>
	</Sql>

	<Param name="companyId" type="UniqueIdentifier"/>
</AdoNetDataAccess>
